package com.hzdl.cinema.dao.impl;

import com.hzdl.cinema.dao.ScheduleDao;
import com.hzdl.cinema.domain.Play;
import com.hzdl.cinema.domain.Schedule;
import com.hzdl.cinema.domain.Seat;
import com.hzdl.cinema.domain.Studio;
import com.hzdl.cinema.util.JDBCUtils;
import org.junit.Test;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

public class ScheduleDaoImpl implements ScheduleDao {
    JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());

    //影片计划
    @Override
    public Schedule schedule(String playName) {
//        String s="SELECT * from schedule where play_Id= (select play_Id from play where play_Name = ?)";
        String s = "SELECT * from schedule t1,(select play_Id ,play_Name from play where play_Name = ?)t2 ,studio t3  where t2.play_Id =t1.play_Id and t1.studio_Id=t3.studio_Id";
        Schedule schedule = null;
        try {
            schedule = jdbcTemplate.queryForObject(s, new BeanPropertyRowMapper<>(Schedule.class), playName);
        } catch (DataAccessException e) {
//            e.printStackTrace();
        }
        return schedule;
    }

    @Override
    public Play findPlay(String playName) {
        String s = "select * from play where play_Name=?";
        Play play = null;
        try {
            play = jdbcTemplate.queryForObject(s, new BeanPropertyRowMapper<>(Play.class), playName);
        } catch (DataAccessException e) {
//            e.printStackTrace();
        }
        return play;
    }

    @Override
    public Studio findStudio(Integer studioId) {
        String s = "select * from studio where studio_Id=?";
        Studio studio = null;
        try {
            studio = jdbcTemplate.queryForObject(s, new BeanPropertyRowMapper<>(Studio.class), studioId);
        } catch (DataAccessException e) {
//            e.printStackTrace();
        }
        return studio;
    }



  @Override
    public List<Schedule> seeSchedule() {
        String sql="select * from schedule";
        List<Schedule> scheduleList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Schedule.class));
        return scheduleList;
    }

    @Override
    public Studio seeStudio(String studioId) {
        String sql ="select * from studio where studio_Id=?";
        Studio studio =null;
        try {
         studio = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Studio.class), studioId);
        }catch (Exception e){
        }
        return studio;
    }

    @Override
    public Schedule findMySchedule(Integer scheduleId) {
        String sql="select * from schedule where schedule_Id=?";
        Schedule schedule = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Schedule.class), scheduleId);
        return schedule;
    }
    @Override
    public List<Seat> searchSeat(String scheduleId) {
        String s="select * from seat where studio_Id =(select studio_Id from schedule where schedule_Id=?)";
        List<Seat> seatList = jdbcTemplate.query(s, new BeanPropertyRowMapper<>(Seat.class),scheduleId);
        return seatList;
    }

    @Override
    public Studio searchStudio(String scheduleId) {
        String s="SELECT * from studio where studio_Id=(select studio_Id from schedule where schedule_Id =?)";
        Studio studio = jdbcTemplate.queryForObject(s, new BeanPropertyRowMapper<>(Studio.class),scheduleId);

        return studio;
    }



    @Override
    public List<Schedule> findAllSchedule() {
        String sql = "select * from schedule";
        List<Schedule> scheduleList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Schedule.class));
        return scheduleList;
    }

    @Override
    public Schedule findScheduleById(int scheduleId) {
        String sql = "select * from schedule where schedule_Id = ?";
        Schedule schedule = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Schedule.class), scheduleId);
        return schedule;
    }

    @Override
    public void modifyScheduleById(Integer scheduleId, Date startTime, BigDecimal schedulePrice) {
        String sql="update schedule set start_Time=?,schedule_Price=? where schedule_Id=?";
        jdbcTemplate.update(sql,startTime,schedulePrice,scheduleId);
    }

    @Override
    public void deleteScheduleById(int scheduleId) {
        String sql="delete from schedule where schedule_Id = ? ";
        jdbcTemplate.update(sql,scheduleId);
    }

    @Override
    public void addSchedule(int playId, int studioId, Date startTime, BigDecimal schedulePrice) {
        String sql="insert into schedule values(null,?,?,?,?)";
        jdbcTemplate.update(sql,playId,studioId,startTime,schedulePrice);
    }

    @Override
    public Schedule searchSchedule(int playId) {
        String sql="select * from schedule where play_Id=?";
        Schedule schedule=null;
        try {
            schedule=jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<>(Schedule.class),playId);
        }catch (Exception e){
//            e.printStackTrace();
        }
        return schedule;
    }

    @Test
    public void method() {
        String s="SELECT * from studio where studio_Id=(select studio_Id from schedule where schedule_Id =?)";
        Studio studio = jdbcTemplate.queryForObject(s, new BeanPropertyRowMapper<>(Studio.class),8);


        System.out.println(studio);
    }
}
